<?php
/** PHPExcel */ require_once $PathPrefix .'includes/PHPExcel/Classes/PHPExcel.php';
/** PHPExcel Writer 2005 */ require_once $PathPrefix .'includes/PHPExcel/Classes/PHPExcel/Writer/Excel5.php';
/** PHPExcel Writer 2007 */ require_once $PathPrefix .'includes/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php';
/** PHPExcel Rich Text */ require_once $PathPrefix .'includes/PHPExcel/Classes/PHPExcel/RichText.php';
/** PHPExcel_IOFactory */ include $PathPrefix .'includes/PHPExcel/Classes/PHPExcel/IOFactory.php';

/**
 *
 * @category    API
 * @package     Api_Excel
 * @author      trungpm
 */
class Api_Excel {

	private $_NAME_OF_FILE;
	private $_WRITABLE_DIR;
	public $_OBJ_PHP_Excel;
	private $_OBJ_Excel_Type;

	/**
	 * Construct phpexcel object
	 * @author	trungpm
	 * @return null
	 */
	public function __construct($name_of_file, $writable_dir_ending_in_slash, $excel_type) {		
		$memoryLimit = intval(ini_get('memory_limit'));
		if ($memoryLimit <= 128) {
			ini_set("memory_limit", "128M");
		}
		// set name of file to output to browser and dir writable
		$this->_NAME_OF_FILE = $name_of_file;
		$this->_WRITABLE_DIR = $writable_dir_ending_in_slash;
		$this->_OBJ_Excel_Type = $excel_type;
		// create phpexcel object with PHPExcel()
		$this->_OBJ_PHP_Excel = new PHPExcel();
	}
	
	public function setFileNameExcel($filename) {
		$this->_NAME_OF_FILE = $filename;
	}
	
	/**
	 * load object to read excel file and auto file type
	 * @author	trungpm
	 * @return null
	 */
	public function loadIOFactory() {
		$this->_OBJ_PHP_Excel = null;
		$this->_OBJ_PHP_Excel = PHPExcel_IOFactory::load($this->_WRITABLE_DIR.$this->_NAME_OF_FILE);
	}

	/**
	 * Set up active sheet of file excel by index, start 0
	 * @author	trungpm
	 * @return null
	 */
	public function setActiveSheet($int) {
		$this->_OBJ_PHP_Excel->setActiveSheetIndex($int);
	}

	/**
	 * Set up meta data for file excel to output to browser
	 * @author	trungpm
	 * @return null
	 */
	public function setMetaData($creator = '', $last_modified_by = '', $title = 'title', $subject = '', $description = '', $keyword = '', $category = '') {
		$this->_OBJ_PHP_Excel->getProperties()->setCreator($creator);
		$this->_OBJ_PHP_Excel->getProperties()->setLastModifiedBy($last_modified_by);
		$this->_OBJ_PHP_Excel->getProperties()->setTitle($title);
		$this->_OBJ_PHP_Excel->getProperties()->setSubject($subject);
		$this->_OBJ_PHP_Excel->getProperties()->setDescription($description);
		$this->_OBJ_PHP_Excel->getProperties()->setKeywords($keyword);
		$this->_OBJ_PHP_Excel->getProperties()->setCategory($category);
	}

	/**
	 * Set up page setup for file
	 * @author	trungpm
	 * @return null
	 */
	public function setPageSetup($scale = 80, $paper_size = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4, $orientation = PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getPageSetup()->setScale($scale);
		$this->_OBJ_PHP_Excel->getActiveSheet()->getPageSetup()->setPaperSize($paper_size);
		$this->_OBJ_PHP_Excel->getActiveSheet()->getPageSetup()->setOrientation($orientation);
	}

	/**
	 * Set up Zoom Scale
	 * @author	trungpm
	 * @return null
	 */
	public function setZoomScale($zoomScale) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getSheetView()->setZoomScale($zoomScale);
	}

	/**
	 * Set up page Margins for file
	 * @author	trungpm
	 * @return null
	 */
	public function setPageMargins($top = false, $left = false, $right = false, $bottom = false) {
		if ($top !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getPageMargins()->setTop($top);
		}
		if ($left !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getPageMargins()->setLeft($left);
		}
		if ($right !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getPageMargins()->setRight($right);
		}
		if ($bottom !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getPageMargins()->setBottom($bottom);
		}
		return $this;
	}

	/**
	 * Set up setRowsToRepeatAtTopByStartAndEnd
	 * @author	trungpm
	 * @return null
	 */
	public function setRowsToRepeatAtTopByStartAndEnd($start, $end) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd($start, $end);
		return $this;
	}

	/**
	 * Set up default style
	 * @author	trungpm
	 * @return null
	 */
	public function setDefaultStyle($font, $size) {
		$this->_OBJ_PHP_Excel->getDefaultStyle()->getFont()->setName($font);
		$this->_OBJ_PHP_Excel->getDefaultStyle()->getFont()->setSize($size);
	}

	/**
	 * Set up break column
	 * @author	trungpm
	 * @return null
	 */
	public function setBreak($cell) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->setBreak($cell, PHPExcel_Worksheet::BREAK_COLUMN);
		return $this;
	}

	/**
	 * Set up WrapText
	 * @author	trungpm
	 * @return null
	 */
	public function setWrapText($cell) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getAlignment()->setWrapText(true);
		return $this;
	}

	/**
	 * Set up cell forefround color
	 * @author	trungpm
	 * @return null
	 */
	public function setCellForegroundColor($cell, $colorRGB) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getFont()->getColor()->setRGB($colorRGB);
		return $this;
	}

	/**
	 * Set up cell bold
	 * @author	trungpm
	 * @return null
	 */
	public function setCellBold($cellOrArray, $isBold) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFont()->setBold($isBold);
		return $this;
	}

	/**
	 * Set up cell underline
	 * @author	trungpm
	 * @return null
	 */
	public function setCellUnderline($cellOrArray, $isUnderline) {
		if ($isUnderline !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
		}
		return $this;
	}

	/**
	 * Set up cell font size
	 * @author	trungpm
	 * @return null
	 */
	public function setCellFontSize($cellOrArray, $size) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFont()->setSize($size);
		return $this;
	}

	/**
	 * Set up cell italic
	 * @author	trungpm
	 * @return null
	 */
	public function setCellItalic($cellOrArray, $isItalic) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFont()->setItalic($isItalic);
		return $this;
	}

	/**
	 * Set up cell background color
	 * @author	trungpm
	 * @return null
	 */
	public function setCellBackgroundColor($cellOrArray, $fillType, $color) {
		$BackgroundColor = new PHPExcel_Style_Color();
		$BackgroundColor->setRGB($color);
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFill()->setFillType($fillType);
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFill()->setStartColor($BackgroundColor);
		return $this;
	}

	/**
	 * Set up cell font
	 * @author	trungpm
	 * @return null
	 */
	public function setCellFont($cellOrArray, $font) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cellOrArray)->getFont()->setName($font);
		return $this;
	}

	/**
	 * Set up cell value explicit
	 * @author	trungpm
	 * @return null
	 */
	public function setValueExplicit($cell, $value, $type) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getCell($cell)->setValueExplicit($value, $type);
		return $this;
	}

	/**
	 * Merge cells
	 * @author	trungpm
	 * @return null
	 */
	public function mergeCells($top_left, $bottom_right) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->mergeCells("$top_left:$bottom_right");
		return $this;
	}

	/**
	 * Set up text of file excel at cell, text, bold, underline, size, font
	 * @author	trungpm
	 * @return null
	 */
	public function setText($cell, $text, $bold = false, $underline = false, $italic = false, $size = false, $font = false) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->SetCellValue($cell, $text);
		if ($bold !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getFont()->setBold(true);
		}
		if ($underline !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
		}
		if ($size !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getFont()->setSize($size);
		}
		if ($italic !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getFont()->setItalic($italic);
		}
		if ($font !== false) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getFont()->setName($font);
		}
		return $this;
	}

	/**
	 * Set up horizontal allignment for cell in excel file
	 * @author	trungpm
	 * @return null
	 */
	public function setCellHorizontalAllignment($cell, $alignment = '(center|left|right)') {
		switch (strtolower($alignment)) {
			case "center":
				$align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
				break;
			case "left":
				$align = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
				break;
			case "right":
				$align = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
				break;
			default:
				$align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
				break;
		}
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getAlignment()->setHorizontal($align);
		return $this;
	}

	/**
	 * Set up vertical allignment for cell in excel file
	 * @author	trungpm
	 * @return null
	 */
	public function setCellVerticalAllignment($cell, $alignment = '(center|bottom|justify|top)') {
		switch (strtolower($alignment)) {
			case "center":
				$align = PHPExcel_Style_Alignment::VERTICAL_CENTER;
				break;
			case "bottom":
				$align = PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
				break;
			case "justify":
				$align = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY;
				break;
			case "top":
				$align = PHPExcel_Style_Alignment::VERTICAL_TOP;
				break;
			default:
				$align = PHPExcel_Style_Alignment::VERTICAL_CENTER;
				break;
		}
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getAlignment()->setVertical($align);
		return $this;
	}

	/**
	 * Set up column width with column name, width size
	 * @author	trungpm
	 * @return null
	 */
	public function setColumnWidth($column = 'A', $width_inches = 12, $auto_size = false) {
		if ($auto_size) {
			$this->_OBJ_PHP_Excel->getActiveSheet()->getColumnDimension($column)->setAutoSize($auto_size);
			return;
		}
		$this->_OBJ_PHP_Excel->getActiveSheet()->getColumnDimension($column)->setWidth($width_inches);
		return $this;
	}
	
	/**
	 * Set up hide or unhide column
	 * @author	trungpm
	 * @return null
	 */
	public function setHideColumn($column = 'A', $hide = false) {		
		$this->_OBJ_PHP_Excel->getActiveSheet()->getColumnDimension($column)->setVisible($hide);		
		return $this;
	}
	
	/**
	 * Set up row width with row number, width size
	 * @author	trungpm
	 * @return null
	 */
	public function setRowHeight($row = '1', $height_inches = 25) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getRowDimension($row)->setRowHeight($height_inches);
		return $this;
	}

	/**
	 * Set up array style for cell in excel file
	 * @author	trungpm
	 * @return null
	 */
	public function setApplyFromArray($cell, $arrayStyle) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->applyFromArray($arrayStyle);
		return $this;
	}

	/**
	 * Set up duplicate style for top left to bottom right with array
	 * @author	trungpm
	 * @return null
	 */
	public function setDuplicateStyleArray($arrayStyle, $top_left, $bottom_right) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->duplicateStyleArray($arrayStyle, "$top_left:$bottom_right");
		return $this;
	}

	/**
	 * Set up format code
	 * @author	trungpm
	 * @return null
	 * http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=35056
	 */
	public function setFormatCode($cell, $format) {
		$this->_OBJ_PHP_Excel->getActiveSheet()->getStyle($cell)->getNumberFormat()->setFormatCode($format);
		return $this;
	}

	/**
	 * load object to read excel file and auto file type
	 * @author	trungpm
	 * @return null
	 */
	public function getRowCount() {
		return $this->_OBJ_PHP_Excel->getActiveSheet()->getHighestRow();
	}

	/**
	 * load object to read excel file and auto file type
	 * @author	trungpm
	 * @return null
	 */
	public function getCellByColumnAndRow($column, $row) {
		return $this->_OBJ_PHP_Excel->getActiveSheet()->getCellByColumnAndRow($column, $row)->getValue();
	}

	public function getCalculatedValue($cell) {
		return $this->_OBJ_PHP_Excel->getActiveSheet()->getCell($cell)->getCalculatedValue();
	}

	/**
	 * Output to browser
	 * @author	trungpm
	 * @return null
	 * http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=35056
	 */
	public function outputToBrowser() {
		$objWriter = null;
		if (!is_writable($this->_WRITABLE_DIR)) {
			throw new Exception("Directory: $this->_WRITABLE_DIR is not writable.");
		}
		
		switch ($this->_OBJ_Excel_Type) {
			case 'Excel5':
				$objWriter = new PHPExcel_Writer_Excel5($this->_OBJ_PHP_Excel);
				$filename = $this->_WRITABLE_DIR.rand(1, 99999999).'.xls';
				$objWriter->save($filename);
				header("Expires: 0");
				header("Cache-Control: private");
				header("Content-type: application/vnd.ms-excel");
				header("Content-type: application/x-msexcel");
				header("Content-Disposition: attachment; filename=$this->_NAME_OF_FILE.xls");
				header('Content-Transfer-Encoding: binary');
				header("Accept-Ranges: bytes");
				header("Cache-Control: no-store, no-cache, must-revalidate");
				header("Cache-Control: post-check=0, pre-check=0", false);
				header("Pragma: no-cache");			
				readfile($filename);
				unlink($filename);
				break;
			case 'Excel2007':
				$objWriter = new PHPExcel_Writer_Excel2007($this->_OBJ_PHP_Excel);
				$filename = $this->_WRITABLE_DIR.rand(1, 99999999).'.xlsx';
				$objWriter->save($filename);
				header("Pragma: public");
				header("Expires: 0");
				header("Cache-Control: private");
				header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
				header("Content-Disposition: attachment; filename=$this->_NAME_OF_FILE.xlsx");
				header('Content-Transfer-Encoding: binary');
				header("Accept-Ranges: bytes");
				readfile($filename);
				unlink($filename);
				break;
		}
		exit;
	}

	/**
	 * set freeze pane
	 * @author	bichttn
	 * @return null
	 */
	public function setFreezePane($cell) {
		return $this->_OBJ_PHP_Excel->getActiveSheet()->freezePane($cell);
	}

	/**
	 * Set center page horizontally
	 *
	 * @author	tuvv
	 * @param bool $value
	 * @return PHPExcel_Worksheet_PageSetup
	 */
	public function setHorizontalCentered($value = false) {
		if($value != false){
			return $this->_OBJ_PHP_Excel->getActiveSheet()->getPageSetup()->setHorizontalCentered($value);
		}
	}

	 /**
     * Create sheet and add it to this workbook
     *
     * @author	tuvv
     * @return PHPExcel_Worksheet
     */
    public function createSheet($iSheetIndex = false)
    {
    	if($iSheetIndex != false){
			return $this->_OBJ_PHP_Excel->createSheet($iSheetIndex);
    	}
    }

     /**
     * Set title
     *
     * @author	tuvv
     * @param string $pValue String containing the dimension of this worksheet
	 * @return PHPExcel_Worksheet
     */
    public function setTitle($pValue = false)
    {
    	if($pValue != false){
			return $this->_OBJ_PHP_Excel->getActiveSheet()->setTitle($pValue);
    	}
    }

    /**
     * set image
     *
     * @author tuvv
     * @return null
     */
    public function createImage($urlImage = false, $nameImage = false, $cell = false, $imageHeight = false, $imageWidth = false){
    	$objDrawing = new PHPExcel_Worksheet_Drawing();
    	if(($urlImage != false) && ($nameImage != false)){
    		$objDrawing->setPath($urlImage.$nameImage);
    	}
		if($cell != false){
			$objDrawing->setCoordinates($cell);
		}
		if($imageHeight != false){
			$objDrawing->setHeight($imageHeight);
		}
		if($imageWidth != false){
			$objDrawing->setWidth($imageWidth);
		}
		$objDrawing->setWorksheet($this->_OBJ_PHP_Excel->getActiveSheet());
    }

    /**
     * set footer header size
     *
     * @author tuvv
     * @return null
     */
    public function setFooterHeader($header = false, $footer = false){
    	if($footer != false){
    		$this->_OBJ_PHP_Excel->getActiveSheet()->getPageMargins()->setFooter($footer);
    	}
    	if($header != false){
    		$this->_OBJ_PHP_Excel->getActiveSheet()->getPageMargins()->setHeader($header);
    	}
    }

	/**
	 * Set print area
	 *
	 * @author tuvv
	 * @param int $column1		Column 1
	 * @param int $row1			Row 1
	 * @param int $column2		Column 2
	 * @param int $row2			Row 2
	 * @return PHPExcel_Worksheet_PageSetup
	 */
	public function setPrintAreaByColumnAndRow($column1 = false, $row1 = false, $column2 = false, $row2 = false) {
		if($column1 != false && $row1 != false && $column2 != false && $row2 != false){
			return $this->_OBJ_PHP_Excel->getActiveSheet()->getPageSetup()->setPrintAreaByColumnAndRow($column1, $row1, $column2, $row2);
		}
	}

}
